<?php
/*
#mysql命令 
huakoulog
BEGIN

SELECT 
customer_bill_pay_log.consume_id,
customer_bill_pay_log.customer_id,
customer_bill_pay_log.create_time,
customer_bill_pay_log.pay_action,
customer_bill_pay_log.ret_msg,
customer_bank_info.gate_id,
customer_bill_pay_log.amount/100 as amount,
consume_history.status,
DATE_FORMAT(from_unixtime(consume_history.repayment_time),"%Y-%m-%d") as repayment_time,
DATE_FORMAT(from_unixtime(consume_history.update_time),"%Y-%m-%d") as update_time

FROM customer_bill_pay_log
LEFT JOIN consume_history on consume_history.id = customer_bill_pay_log.consume_id
LEFT JOIN customer_bank_info on customer_bank_info.customer_id = customer_bill_pay_log.customer_id

where 
customer_bank_info.is_default = 1
and customer_bill_pay_log.create_time >= '2017-11-27'
and DATE_FORMAT(from_unixtime(consume_history.repayment_time),"%Y-%m-%d") = DATE_FORMAT(customer_bill_pay_log.create_time,"%Y-%m-%d")

order by consume_history.repayment_time
;
END
 
还款未续借-3天 
 
 BEGIN

SELECT 
consume_history.id,
customer.real_name as '姓名',
customer.mobile as '电话',
FROM_UNIXTIME(consume_history.repayment_time) as '到期日期'
FROM consume_history
LEFT JOIN customer ON customer.id = consume_history.customer_id
WHERE 
consume_history.type = 5 and consume_history.status in (3)
# 到期日前三天
and FROM_UNIXTIME(consume_history.repayment_time) BETWEEN DATE_SUB(NOW(),INTERVAL 8 DAY) and DATE_SUB(NOW(),INTERVAL 1 DAY)
# 正常还款
and DATEDIFF(FROM_UNIXTIME(consume_history.repayment_time),FROM_UNIXTIME(consume_history.update_time)) >= 0
# 为续借
and consume_history.customer_id NOT IN (SELECT customer_id FROM consume_history WHERE type = 4 and `status` = 5)
and consume_history.customer_id NOT IN (SELECT customer_id FROM consume_history WHERE type = 5 and `status` = 1)

;

END


p_caiwu
BEGIN

SELECT 
ConsumeHistory.id AS '订单号',
Customer.id AS '客户ID',
Customer.real_name AS '姓名',
Customer.mobile AS '用户手机号',
#CONCAT(CPL.order_id,"'") AS 'order_id',
CASE WHEN ConsumeHistory.repay_id in (2,6) AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN '14天' 
WHEN ConsumeHistory.repay_id = 4 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN '21天'
WHEN ConsumeHistory.repay_id = 5 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN '28天'
ELSE '30天' END '产品',
CASE WHEN ISNULL(TCH.consume_id) THEN '找钱花'
ELSE '融360' END order_no,
ROUND(ConsumeHistory.amount/100,2) AS '实付金额',
ROUND(ConsumeHistory.consume_amount/100,2) AS '借款金额',
DATE_FORMAT(FROM_UNIXTIME(ConsumeHistory.add_time),"%Y-%m-%d") AS '提现日期',
DATE_FORMAT(FROM_UNIXTIME(ConsumeHistory.repayment_time),"%Y-%m-%d") AS '应还款时间',
CASE ConsumeHistory.status
WHEN '1' THEN '未还款'
WHEN '3' THEN '已还款'
ELSE '未知状态' END '是否还款',
CASE WHEN ConsumeHistory.status in (1,3) THEN FROM_UNIXTIME(ConsumeHistory.update_time) ELSE NULL END '实际还款日期',
CASE WHEN ConsumeHistory.status in (1,3) THEN ROUND(CBPL.amount/100,2) ELSE '' END '实际还款金额',

CASE WHEN ConsumeHistory.status = 3 THEN '已还款'
WHEN DATEDIFF(NOW(),FROM_UNIXTIME(ConsumeHistory.repayment_time)) > 0 THEN '逾期'
ELSE '待还款' END '逾期状态',

CASE WHEN ConsumeHistory.repay_id in (2,6) AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN 
			(round(DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time))/14,0)-1)*(ConsumeHistory.consume_amount/100-ConsumeHistory.amount/100)
WHEN ConsumeHistory.repay_id = 4 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN 
			(round(DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time))/21,0)-1)*(ConsumeHistory.consume_amount/100-ConsumeHistory.amount/100)
WHEN ConsumeHistory.repay_id = 5 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN 
			(round(DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time))/28,0)-1)*(ConsumeHistory.consume_amount/100-ConsumeHistory.amount/100)
ELSE (round(DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time))/30,0)-1)*(ConsumeHistory.consume_amount/100-ConsumeHistory.amount/100)
END '展期费用'

FROM consume_history ConsumeHistory
LEFT JOIN customer Customer ON Customer.id = ConsumeHistory.customer_id
LEFT JOIN thirdparty_consume_history TCH ON TCH.consume_id = ConsumeHistory.id
LEFT JOIN (SELECT consume_id,sum(amount) as amount FROM customer_bill_pay_log WHERE pay_time > 0 group by consume_id) CBPL ON CBPL.consume_id = ConsumeHistory.id
#LEFT JOIN (SELECT consume_id,order_id FROM customer_pay_log WHERE ret_code = '0000' or ret_msg = 'success') CPL on CPL.consume_id = ConsumeHistory.id

WHERE ( ConsumeHistory.type IN(5) AND ConsumeHistory.status IN (1,3) )
AND ConsumeHistory.customer_id NOT IN (2162,2137,3303) # 朱江 张密 楼瑞超
AND  ConsumeHistory.amount > 1
AND Customer.real_name IS NOT NULL
;
END



p_liushui
BEGIN

SELECT
distinct CBPL.id,
CONCAT(CBPL.order_id,"'") AS 'order_id',
ConsumeHistory.id AS '订单号',
Customer.id AS '客户ID',
Customer.real_name AS '姓名',
Customer.mobile AS '用户手机号',

ROUND(ConsumeHistory.amount/100,2) AS '实付金额',
ROUND(ConsumeHistory.consume_amount/100,2) AS '借款金额',
DATE_FORMAT(FROM_UNIXTIME(ConsumeHistory.add_time),"%Y-%m-%d") AS '提现日期',
DATE_FORMAT(FROM_UNIXTIME(ConsumeHistory.repayment_time),"%Y-%m-%d") AS '应还款时间',

FROM_UNIXTIME(CBPL.pay_time) AS '实际还款日期',
CBPL.amount/100 AS '实际还款金额',
CBPL.pay_action AS '还款方式',
CBPL.publish AS '退款',
CBPL.order_id

FROM customer_bill_pay_log CBPL 
LEFT JOIN customer Customer ON Customer.id = CBPL.customer_id
LEFT JOIN consume_history ConsumeHistory ON ConsumeHistory.id = CBPL.consume_id
WHERE 
CBPL.pay_time > 0
AND Customer.real_name IS NOT NULL
AND ConsumeHistory.customer_id NOT IN (2162,2137,3303)
AND  ConsumeHistory.amount > 1
AND ConsumeHistory.type = 5 and ConsumeHistory.status in (1,3)

;
END


p_overdue
BEGIN

DROP TABLE IF EXISTS ClientId;
CREATE TEMPORARY TABLE IF NOT EXISTS ClientId(
  customer_id int(11) NOT NULL DEFAULT '0' COMMENT '客户ID',
  client_id int(11) NOT NULL DEFAULT '0' COMMENT '渠道ID',
  KEY customer_id (`customer_id`) USING BTREE,
  KEY client_id (`client_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT substring(uuid,1,LOCATE('#',uuid)-1) as customer_id,client_id
FROM client_uuid
WHERE client_uuid.uuid LIKE "%#%";

DROP TABLE IF EXISTS zhanqi_yang;
CREATE TEMPORARY TABLE IF NOT EXISTS zhanqi_yang(
  id int(11) NOT NULL DEFAULT '0' COMMENT '客户ID',
  gap int(11) NOT NULL DEFAULT '0' COMMENT 'gap',
  KEY id (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT 
#customer_id,
id,
#FROM_UNIXTIME(repayment_time),
#FROM_UNIXTIME(add_time),
CASE WHEN DATEDIFF(DATE_SUB(FROM_UNIXTIME(repayment_time,"%Y-%m-%d 00:00:00"), INTERVAL 30 DAY),DATE_SUB(FROM_UNIXTIME(add_time,"%Y-%m-%d 00:00:00"), INTERVAL -29 DAY)) > 0
THEN DATEDIFF(DATE_SUB(FROM_UNIXTIME(repayment_time,"%Y-%m-%d 00:00:00"), INTERVAL 30 DAY),DATE_SUB(FROM_UNIXTIME(add_time,"%Y-%m-%d 00:00:00"), INTERVAL -29 DAY))
ELSE 0 END 'gap'
FROM consume_history ConsumeHistory
WHERE ( ConsumeHistory.type IN(5) AND ConsumeHistory.status IN (1,3) )
AND ConsumeHistory.customer_id NOT IN (2162,2137,3303) # 朱江 张密 楼瑞超
AND DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time)) > 31;

SELECT 
ConsumeHistory.id AS '订单号',
Customer.id AS '客户ID',
Customer.real_name AS '姓名',
CONCAT(Customer.id_num,"'") AS '身份证号',
Customer.birthplace AS '身份证住址',
Customer.mobile AS '用户手机号',
CEI.company_address as '工作地址',
CEI.user_address AS '常住地址',
#Customer.sesame_score AS '芝麻分',
ClientId.client_id as '渠道来源',

CASE WHEN ConsumeHistory.repay_id = 2 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN '14天' 
WHEN ConsumeHistory.repay_id = 4 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN '21天'
WHEN ConsumeHistory.repay_id = 5 AND ConsumeHistory.add_time>UNIX_TIMESTAMP("2017-07-08 18:00:00") THEN '28天'
ELSE '30天' END '产品',

CONCAT(CBI.card_num,"'") AS '银行卡号',
CBI.gate_id AS '放款银行',
ROUND(ConsumeHistory.amount/100,2) AS '实付金额',
ROUND(ConsumeHistory.consume_amount/100,2) AS '借款金额',
FROM_UNIXTIME(ConsumeHistory.add_time) AS '提现日期',
FROM_UNIXTIME(ConsumeHistory.repayment_time) AS '应还款时间',
CASE ConsumeHistory.status
WHEN '1' THEN '未还款'
WHEN '3' THEN '已还款'
ELSE '未知状态' END '是否还款',
CASE WHEN ConsumeHistory.status = '3' THEN FROM_UNIXTIME(ConsumeHistory.update_time) ELSE NULL END '实际还款日期',
CASE WHEN ConsumeHistory.status = '3' THEN ROUND(CBPL.amount/100,2) ELSE '' END '实际还款金额',

CASE WHEN ConsumeHistory.status =3 THEN '已还款'
WHEN DATEDIFF(NOW(),FROM_UNIXTIME(ConsumeHistory.repayment_time)) > 0 THEN '逾期'
ELSE '待还款' END '逾期状态',

CASE WHEN (ConsumeHistory.status = 1 AND DATEDIFF(NOW(),FROM_UNIXTIME(ConsumeHistory.repayment_time)) > 0) 
THEN FLOOR((UNIX_TIMESTAMP(NOW())-ConsumeHistory.repayment_time)/86400) ELSE 0 END '逾期天数',

CASE WHEN (ConsumeHistory.status = 1 AND DATEDIFF(NOW(),FROM_UNIXTIME(ConsumeHistory.repayment_time)) > 0) 
THEN ROUND(FLOOR((UNIX_TIMESTAMP(NOW())-ConsumeHistory.repayment_time)/86400)*0.01*ROUND(ConsumeHistory.consume_amount/100,2),2) ELSE 0 END '逾期违约金应收金额',

CASE WHEN CBPL.amount > ConsumeHistory.consume_amount THEN ROUND((CBPL.amount - ConsumeHistory.consume_amount)/100,2) ELSE 0 END '逾期违约金实收金额',

CASE WHEN DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time)) > 31 THEN '展期' ELSE '' END '是否展期',
CASE WHEN DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time)) > 31 THEN FROM_UNIXTIME(ConsumeHistory.repayment_time)
ELSE '' END '最后展期日期',

CASE WHEN DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time)) > 31
THEN ZQ.gap*ROUND(ConsumeHistory.consume_amount/100,2)*0.01+ROUND(ConsumeHistory.consume_amount/100,2)*0.2*(ROUND(DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time))/30,0)-1)
ELSE '' END '展期服务费总金额',

CASE WHEN DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time)) > 31 
THEN ROUND(DATEDIFF(FROM_UNIXTIME(ConsumeHistory.repayment_time),FROM_UNIXTIME(ConsumeHistory.add_time))/30,0)-1
ELSE '' END '展期期数'
FROM consume_history ConsumeHistory
LEFT JOIN customer_extra_info CEI ON CEI.customer_id = ConsumeHistory.customer_id
LEFT JOIN ClientId ON ConsumeHistory.customer_id=ClientId.customer_id
LEFT JOIN customer Customer ON Customer.id = ConsumeHistory.customer_id
LEFT JOIN (SELECT consume_id,sum(amount) as amount FROM customer_bill_pay_log WHERE pay_time > 0 group by consume_id) CBPL ON CBPL.consume_id = ConsumeHistory.id
LEFT JOIN
(SELECT DISTINCT customer_id,card_user_name,card_num,gate_id
FROM customer_bank_info GROUP BY customer_id) CBI ON CBI.customer_id = ConsumeHistory.customer_id
LEFT JOIN zhanqi_yang ZQ ON ZQ.id = ConsumeHistory.id
LEFT JOIN 
(SELECT DISTINCT customer_id,MAX(FROM_UNIXTIME(a.add_time)) AS add_time
FROM consume_history a WHERE type IN(4) AND status IN (12,14) GROUP BY customer_id) SQ ON SQ.customer_id = ConsumeHistory.customer_id

WHERE ( ConsumeHistory.type IN(5) AND ConsumeHistory.status IN (1,3) )
AND ConsumeHistory.customer_id NOT IN (2162,2137,3303) # 朱江 张密 楼瑞超
AND  ConsumeHistory.amount > 1
;
END




p_贷后回款
BEGIN

select 
Customer.id as '客户ID',
Consume_User_Log.consume_id as '消费ID',
Customer.real_name as '客户姓名',
Customer.mobile as '客户手机',
FROM_UNIXTIME(Consume_User_Log.add_time) as '分案时间',
Admin_User2.user_login as '操作人',
Consume_User_Log.user_id as 'usercode',
Admin_User.user_login as '催收员',
Consume_User_Log.`status` as '分案状态',

CASE when Consume_History.`status` = 3 then '已还款'
when Consume_History.`status` = 1 and FROM_UNIXTIME(Consume_History.repayment_time) > now() then '展期'
else '逾期' end '当前状态',

FROM_UNIXTIME(Consume_History.repayment_time) as '账单日期',
FROM_UNIXTIME(Consume_History.update_time) as '还款日期',
round(Consume_History.consume_amount/100,2) as '还款金额',

CASE WHEN Consume_History.update_time > 0 THEN round(Consume_History.consume_amount/100,2)
ELSE 0 END '催回本金',
CASE WHEN Consume_History.status in (1,3) THEN ROUND(CBPL.amount/100,2) ELSE '' END '实际还款金额',
case when DATEDIFF(FROM_UNIXTIME(Consume_History.update_time),FROM_UNIXTIME(Consume_History.repayment_time)) > 0 
then DATEDIFF(FROM_UNIXTIME(Consume_History.update_time),FROM_UNIXTIME(Consume_History.repayment_time)) 
else '' end '逾期还款天数',

case when Consume_History.update_time = 0 and FROM_UNIXTIME(Consume_History.repayment_time) < now() 
then DATEDIFF(now(),FROM_UNIXTIME(Consume_History.repayment_time)) else 0 end '当前逾期天数'

FROM consume_user_log Consume_User_Log
LEFT JOIN consume_history Consume_History ON Consume_History.id = Consume_User_Log.consume_id
left join customer Customer on Customer.id = Consume_History.customer_id
left JOIN admin_users Admin_User on Admin_User.id = Consume_User_Log.user_id
left JOIN admin_users Admin_User2 on Admin_User2.id = Consume_User_Log.admin_id
LEFT JOIN (SELECT consume_id,sum(amount) as amount FROM customer_bill_pay_log WHERE pay_time > 0 group by consume_id) CBPL ON CBPL.consume_id = Consume_History.id

where 
Consume_User_Log.add_time = (
SELECT add_time FROM consume_user_log a WHERE a.consume_id = Consume_User_Log.consume_id
ORDER BY add_time desc LIMIT 0,1
) and
#FROM_UNIXTIME(Consume_User_Log.add_time) <= '2017-11-30 18:00:00' and
Customer.id is not null
#and FROM_UNIXTIME(Consume_User_Log.add_time) >= '2017-09-30 18:00:00'
#and (FROM_UNIXTIME(Consume_History.update_time) >= '2017-10-01' or FROM_UNIXTIME(Consume_History.update_time) ='1970-01-01 08:00:00')
;
END
当天未返回状态
BEGIN

SET @rownum=0;
SELECT 
@rownum:=@rownum+1 AS 'ID',
ConsumeHistory.id AS '消费ID',
Customer.id AS '客户ID',
Customer.real_name AS '真实姓名',
Customer.mobile AS '手机号',
CASE WHEN ISNULL(TCH.order_no) THEN '找钱花'
ELSE '融360' END order_no,
from_unixtime(Customer.add_time) AS '注册时间',
CASE ConsumeHistory.status
WHEN '1' THEN '订单已完成'
WHEN '3' THEN '已还款'
WHEN '6' THEN '审核未通过'
WHEN '7' THEN '决策执行失败'
WHEN '5' THEN '待审核'
WHEN '9' THEN '订单已取消'
WHEN '10' THEN '待审核'
WHEN '11' THEN '自动拒绝'
WHEN '12' THEN '人工审核通过'
ELSE '提现失败' END '状态',
FROM_UNIXTIME(ConsumeHistory.add_time) AS '提现日期',
FROM_UNIXTIME(ConsumeHistory.repayment_time) AS '账单日',
ROUND((ConsumeHistory.amount/100),2) AS '到手金额',
ROUND((consume_amount/100),2) AS '消费金额',
CASE WHEN ConsumeHistory.update_time = 0 THEN NULL ELSE from_unixtime(ConsumeHistory.update_time) END '还款时间'

FROM consume_history ConsumeHistory

LEFT JOIN thirdparty_consume_history TCH ON TCH.consume_id = ConsumeHistory.id
LEFT JOIN customer Customer ON Customer.id = ConsumeHistory.customer_id

WHERE ConsumeHistory.repayment_time < (SELECT UNIX_TIMESTAMP(NOW()))
AND ConsumeHistory.repayment_time >= (SELECT UNIX_TIMESTAMP(NOW())-86400)
AND ( ConsumeHistory.type IN(5) AND ConsumeHistory.status IN (1,3) )
AND  ConsumeHistory.amount > 1
ORDER BY ConsumeHistory.status,ConsumeHistory.update_time DESC
;
END


注册未申请
BEGIN

select real_name,mobile,FROM_UNIXTIME(add_time) as add_time FROM customer
where 
FROM_UNIXTIME(add_time) BETWEEN DATE_SUB(NOW(),INTERVAL 3 DAY) and DATE_SUB(NOW(),INTERVAL 15 MINUTE)
and id not in (
	select customer_id from consume_history 
	where type = 4 and FROM_UNIXTIME(add_time) 
	BETWEEN DATE_SUB(NOW(),INTERVAL 1 DAY) and DATE_SUB(NOW(),INTERVAL 15 MINUTE)
)
;
END

主动还款失败
BEGIN

SELECT

customer_bill_pay_log.id,
customer_bill_pay_log.create_time as '失败时间',
customer_bill_pay_log.ret_msg as '失败原因',
customer.real_name as '姓名',
customer.mobile as '手机号',
CONCAT(customer_bank_info.card_num,"'") as '银行卡号',
customer_bank_info.gate_id as '开户行',
FROM_UNIXTIME(consume_history.add_time) as '交易日期',
FROM_UNIXTIME(consume_history.repayment_time) as '账单日期',
consume_history.consume_amount/100 as '合同金额'


from customer_bill_pay_log
LEFT JOIN customer on customer.id = customer_bill_pay_log.customer_id
LEFT JOIN customer_bank_info on customer_bank_info.customer_id = customer_bill_pay_log.customer_id
LEFT JOIN consume_history on consume_history.id = customer_bill_pay_log.consume_id

where customer_bill_pay_log.pay_action = 1 
and customer_bill_pay_log.ret_msg <> 'success'
and customer_bill_pay_log.create_time >= '2017-12-12'
and customer_bank_info.is_default = 1
and customer_bill_pay_log.ret_msg not like "%余额%"
and consume_history.status = 1

group by customer.mobile
ORDER BY customer_bill_pay_log.create_time


;
end
*/
?>